﻿CREATE proc [dbo].[pro_Get_OrderCaseAnaly]
(
  @Years varchar(50),
  @Procate1 int,
  @ProCate2 int,
  @ProductId int,
  @CompanyId int,
  @TypeId int
)
as
Begin
Declare @cSql nvarchar(4000),@cWhere nvarchar(500)

Declare @StartTime varchar(50),@EndTime  varchar(50),@Product nvarchar(max)
set @StartTime=@Years + '-01'
set @EndTime=@Years+'-12'

 If @ProCate1= '-1'
   Set @cWhere=' ProductId in (select Id from Product)'
 Else
    Begin
	   If @ProCate2 = '-1'
	        Set @cWhere=' ProductId in (select Id from Product where CateOneId='+convert(varchar(10),@Procate1)+')'
	   Else
	     Begin
			 If @ProductId = '-1'
			   Set @cWhere=' ProductId in (select Id from Product where CateTwoId='+Convert(varchar(10),@ProCate2)+')'
			 Else
			   Set @cWhere=' ProductId='+CONVERT(varchar(10),@ProductId)
		 End
	End

If @TypeId <> '-1'
  
  Set @cWhere = @cWhere+ ' and TypeId = ' + Convert(varchar(10),@TypeId)
  

  Set @cSql = ''
  Set @cSql = @cSql + '
       Select Times,sum(jiaoyupeixun) as 教育培训,sum(nonglinmukuang) as 农林牧矿, sum(shehuifuwu) as 社会服务,sum(canyushipin) as 餐娱食品,sum(ITdianzi) as IT电子,sum(yiliaojiankang) as 医疗健康,sum(jituanzonghe) as 集团综合,
		 sum(zhengfugongyi) as 政府公益, sum(qinggongzhizao) as 轻工制造,sum(jiexiezhonggong) as 机械重工,sum(jienenghuanbao) as 节能环保,sum(jianzhuzhuangshi) as 建筑及装饰,sum(wenhuachuban) as 文化出版,
		(sum(jiaoyupeixun) +sum(nonglinmukuang) + sum(shehuifuwu) +sum(canyushipin) +sum(ITdianzi)+sum(yiliaojiankang) +sum(jituanzonghe) + sum(zhengfugongyi) +sum(qinggongzhizao) +sum(jiexiezhonggong) +sum(jienenghuanbao) +sum(jianzhuzhuangshi)+sum(wenhuachuban)) as MonthCount		
		 From 
		 (Select * from OrderCaseAnaly where '+@cWhere+' and CompanyId='''+Convert(varchar(10),@CompanyId)+''' and Times Between '''+@StartTime+''' and '''+@EndTime+''') as aa group by Times order by Times asc'
Exec(@cSql)

End